﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlTypes  ;
using System.Text.RegularExpressions;

public class PageBase
{
    private string _database;
    private string _errorMessage;
    private SqlTransaction Transaction;

    public string ErrorMessage
    {
        get { return _errorMessage; }
    }

    //constructor
    public PageBase(string database)
    {
	    this._database = database;
    }

    //return sql connection
    private SqlConnection GetConnection() 
    {
       return new SqlConnection(ConfigurationManager.AppSettings.Get("constr") + "database=" + _database);
    }

    /// <summary>
    /// retrun ข้อมูลทั้งหมดของ table เป็น DataSet
    /// </summary>
    public DataSet GetDataSet(string sql)
    {
        _errorMessage = "";
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sql, GetConnection());
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            _errorMessage = ex.Message;
            return null;
        }
    }

    /// <summary>
    /// retrun ข้อมูลทั้งหมดของ table เป็น DataSet
    /// </summary>
    public DataSet GetDataSet(SqlCommand cmd)
    {
        _errorMessage = "";
        cmd.Connection = GetConnection();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        try
        {
            da.Fill(ds);
            return ds;
        }
        catch (Exception ex)
        {
            _errorMessage = ex.Message;
            return null;
        }
    }

    /// <summary>
    /// return ข้อมูลทั้งหมดของ table เป็น DataTable
    /// </summary>
    public DataTable GetDataTable(string sql)
    {
        DataSet ds = GetDataSet(sql);
        if (ds == null)
        {
            return null;
        }
        else
        {
            return ds.Tables[0];
        }
    }

    /// <summary>
    /// return ข้อมูลทั้งหมดของ table เป็น DataTable
    /// </summary>
    public DataTable GetDataTable(SqlCommand cmd)
    {
        DataSet ds = GetDataSet(cmd);
        if (ds == null)
        {
            return null;
        }
        else
        {
            return ds.Tables[0];
        }
    }

    /// <summary>
    /// Excute Command ที่ส่งมา,
    /// return -1 กลับไปแสดงว่ามี error ให้อ่าน error message จาก Property Message,
    /// ถ้าไม่ใช่ -1 แสดงว่าเป็นจำนวน record ที่ถูกดำเนินการ
    /// </summary>
    public SqlResult Excute(SqlCommand cmd)
    {
        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.Value == null)
            {
                param.Value = DBNull.Value;
            }
            else if (param.Value.GetType().Name == "String")
            {
                param.Value = param.Value.ToString().Trim();
            }
        }
        cmd.CommandText = "SET ANSI_WARNINGS OFF;" + cmd.CommandText;
        cmd.Connection = GetConnection();
        _errorMessage = "";
        int effectedRows = -1;
        cmd.Connection.Open();
        try
        {
            effectedRows = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            _errorMessage = ex.Message;
        }
        cmd.Connection.Close();
        return new SqlResult
        {
            EffectedRows = effectedRows,
            ErrorMessage = _errorMessage
        };
    }

    public int Execute(SqlCommand cmd)
    {
        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.Value.GetType().Name == "String")
            {
                param.Value = param.Value.ToString().Trim();
            }
        }
        _errorMessage = "";
        int effectedRows = -1;
        if (Transaction == null)
        {
            cmd.Connection = GetConnection();
            cmd.Connection.Open();
        }
        else
        {
            cmd.Connection = Transaction.Connection;
            cmd.Transaction = Transaction;
        }
        try
        {
            effectedRows = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            _errorMessage = ex.Message;
        }
        if (Transaction == null)
        {
            cmd.Connection.Close();
        }
        return effectedRows;
    }

    public void BeginTrans()
    {
        SqlConnection con = GetConnection();
        con.Open();
        Transaction = con.BeginTransaction();
    }

    public void RollBack()
    {
        if (Transaction != null)
        {
            Transaction.Rollback();
            Transaction = null;
        }
    }

    public object CheckDBNull(object data, object returnData)
    {
        if (data is Nullable)
        {
            return returnData;
        } else {
            return data;
        }
    }

}

public class SqlResult
{
	private int _effectedRows;
	private string _errorMessage;
	public int EffectedRows {
		get { return _effectedRows; }
		set { _effectedRows = value; }
	}
	public string ErrorMessage {
		get { return _errorMessage; }
		set { _errorMessage = value; }
	}
}

